'''
处理流程：
1. 从各个数据库中汇总 tag 为必看的记录到 collect 数据库中的 guochan 表，将原数据库中的相应记录的 status 字段改为 1
2. 根据 magnet 去重（删除重复记录）
3. 删除与 archive 表中 magnet 重复的记录（表 archive 的作用是，排除不同时期的不同数据库中的重复 magnet）
4. 将 title 字段去重后（不删除重复记录）写入 archive 数据表（仅 title 和 magnet 字段）
5. 以 archive 数据表中的 title 为索引，检索 guochan 表中相应记录的信息，写入 final 数据表
6. 根据 final 数据表下载，磁链出问题可到 guochan 表中找其他链接（如果有的话）
7. 下载完毕后仅保留 archive 表中的数据
'''


'''
以下所有均为第一次的代码，后续需要进行一定的修改
'''



from sys import path as path2
from os import path
import json

from rich import print as rprint
path2.append('c:/users/tian/desktop')
from Service import service


# service.path = 'd:/data/collect2.db'

# --------------------------------------------------------------------------------------------------------------------------------------------------------
# 试验田





# --------------------------------------------------------------------------------------------------------------------------------------------------------
# 创建数据表


# service.path = 'd:/data/collect2.db'
# service.exec('create table guochan(num integer primary key autoincrement, title varchar, url varchar, magnet varchar, status int)')
# service.exec('create table final (num integer primary key autoincrement, title varchar, url varchar, magnet varchar)')
# service.exec('create table archive (num integer primary key autoincrement, title varchar, magnet varchar)')

# --------------------------------------------------------------------------------------------------------------------------------------------------------
# 汇总


# collect = []
# files = ['caoliu.db', 'gcbt.db', 'tanhuazu.db', 'taosebt.db']
# for file in files:
#     service.path = path.join('d:/data', file)
#     results = service.query2('select title, url, magnet, status from guochan where tag="必看" and status=0')
#     rprint(f'{file} 中筛选出 {len(results)} 个')
#     collect += results

# service.path = 'd:/data/collect2.db'
# service.exec2('insert into guochan (title, url, magnet, status) values (?, ?, ?, ?)', collect)

# # 删除 magnet 相同的记录
# original = service.query('select count(*) from guochan')[0][0]
# service.exec('delete from guochan where num not in (select min(num) from guochan group by magnet)')
# now  = service.query('select count(*) from guochan')[0][0]
# num = original - now
# rprint('删除重复的数量：', num)
# service.exec('vacuum')        # 清除所有的 vacuum 数据

# # 将原数据库中筛选出来的记录的 status 字段的值改为 1
# files = ['caoliu.db', 'gcbt.db', 'tanhuazu.db', 'taosebt.db']
# for file in files:
#     service.path = path.join('d:/data', file)
#     service.exec('update guochan set status=1 where tag="必看" and status=0')
#     rprint(file)


# --------------------------------------------------------------------------------------------------------------------------------------------------------
# 将 guochan 表中没有重复的记录写入 final 表

# results = service.query2('select title, url, magnet from guochan where status=0 and title in (select title from guochan group by title having count(*)=1)')
# rprint(len(results))
# service.exec2('insert into final (title, url, magnet) values (?, ?, ?)', results)


# --------------------------------------------------------------------------------------------------------------------------------------------------------
# 解决分文件中 magnet 为空的问题


# service.path = 'd:/data/caoliu.db'
# results = service.query2('select link from guochan where magnet is null')
# values = []
# for result in results:
#     link = result[0]
#     if 'rmdown' in link and 'hash' in link:
#         magnet = magnet = 'magnet:?xt=urn:btih:' + link.split('hash=')[-1][3:]
#         values.append((magnet, link))
#     else:
#         rprint(link)
# service.exec2('update guochan set magnet=? where link=?', values)


# --------------------------------------------------------------------------------------------------------------------------------------------------------
# 删除 title 中的无用词汇


# service.path = 'd:/data/collect2.db'
# results = service.query2('select title from final')

# service.path = 'd:/data/test.db'
# # service.exec('create table test (num integer primary key autoincrement, title varchar)')
# with open('d:/data/list.json', 'r', encoding='utf-8') as f:
#     keywords = json.loads(f.read())
# uselessWord = keywords['uselessword']
# values = []
# for result in results:
#     title = result[0]
#     for word in uselessWord:
#         title = title.replace(word, '')
#     values.append((title,))

# service.exec2('insert into test (title) values (?)', values)